package cmcc.gz.cgt.generator;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cmcc.gz.cgt.bean.ColumnBean;
import cmcc.gz.cgt.bean.DatabaseBean;
import cmcc.gz.cgt.bean.TableBean;

/**
 * 数据库信息获得操作类
 * 
 * @ClassName: DatabaseInfoOp
 * @author Andrew.Wen
 * @date 2013-1-10 下午3:56:23
 */
public class DatabaseInfoOp {

	Connection conn = null;

	public DatabaseInfoOp(String classDriver, String url, String username,
			String password) {
		super();
		this.classDriver = classDriver;
		this.url = url;
		this.username = username;
		this.password = password;
	}

	private String classDriver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/test";
	private String username = "root";
	private String password = "root123";

	public Connection getConnectionByJDBC() {
		try {
			// 装载驱动包类
			Class.forName(classDriver);
			// 加载驱动
		} catch (ClassNotFoundException e) {
			System.out.println("装载驱动包出现异常!请查正！");
			e.printStackTrace();
		}
		try {

			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			System.out.println("链接数据库发生异常!");
			e.printStackTrace();
		}
		return conn;
	}

	// public static void test() {
	// String sql = "select * from common_dict";
	// getConnectionByJDBC();
	// try {
	// // 创建一个jdbc声明
	// Statement stmt = conn.createStatement();
	// // 执行查询
	// ResultSet rs = stmt.executeQuery(sql);
	// while (rs.next()) {
	// String username = rs.getString("fieldname");
	// System.out.println(username);
	// }
	// } catch (SQLException e) {
	// System.out.println(e.getMessage());
	// e.printStackTrace();
	// } finally {
	// // 预防性关闭连接（避免异常发生时在try语句块关闭连接没有执行)
	// try {
	// if (conn != null)
	// conn.close();
	// } catch (SQLException e) {
	// System.out.println(e.getMessage());
	// e.printStackTrace();
	// }
	// }
	// }

	// public static void main(String[] args) throws SQLException {
	// DomainGenerator testjdbc = new DomainGenerator();
	//
	// testjdbc.getDbInfo();
	// }

	// private void getTableInfo() throws SQLException {
	//
	// // DataSource dataSource = context.getBean("dataSource",
	// // DataSource.class);
	// // Connection conn = dataSource.getConnection();
	// String tableName = "user";
	// PreparedStatement stmt = conn.prepareStatement("select *  from "
	// + tableName + "");
	// ResultSet resultSet = stmt.executeQuery();
	// ResultSetMetaData rsmd = resultSet.getMetaData();
	// int n = rsmd.getColumnCount();
	// for (int i = 1; i <= n; i++) {
	// String colName = rsmd.getColumnName(i);
	// // String fieldName = StringUtil.toBeanPatternStr(colName);
	// /**//*
	// * 　　 获得ResultSetMeataData对象。所有方法的参数都是列的索引号，即第几列，从1开始 　　
	// */
	// System.out.println("下面这些方法是ResultSetMetaData中方法");
	// System.out
	// .println("============================================================");
	// System.out.println("获得" + i + "列所在的Catalog名字 : "
	// + rsmd.getCatalogName(i));
	// System.out.println("获得" + i + "列对应数据类型的类 "
	// + rsmd.getColumnClassName(i));
	// // System.out.println("获得该ResultSet所有列的数目 " +
	// // rsmd.getColumnCount());
	// System.out.println(i + "列在数据库中类型的最大字符个数"
	// + rsmd.getColumnDisplaySize(i));
	// System.out.println(i + "列的默认的列的标题" + rsmd.getColumnLabel(i));
	// // System.out.println(i+"列的模式 " + rsmd.getSchemaName(i));
	// // System.out.println(i+"列的类型,返回SqlType中的编号 " +
	// // rsmd.getColumnType(i));
	// // System.out.println(i+"列在数据库中的类型，返回类型全名" +
	// // rsmd.getColumnTypeName(i));
	// System.out.println(i + "列类型的精确度(类型的长度): " + rsmd.getPrecision(i));
	// System.out.println(i + "列小数点后的位数 " + rsmd.getScale(i));
	// // System.out.println(i+"列对应的模式的名称（应该用于Oracle） " +
	// // rsmd.getSchemaName(i));
	// System.out.println(i + "列对应的表名 " + rsmd.getTableName(i));
	// System.out.println(i + "列是否自动递增" + rsmd.isAutoIncrement(i));
	// // System.out.println(i+"列在数据库中是否为货币型" + rsmd.isCurrency(i));
	// System.out.println(i + "列是否为空" + rsmd.isNullable(i));
	// // System.out.println(i+"列是否为只读 " + rsmd.isReadOnly(i));
	// System.out.println(i + "列能否出现在where中 " + rsmd.isSearchable(i));
	//
	// // 获取列注释
	// DatabaseMetaData dbmd = conn.getMetaData();
	// ResultSet rs = dbmd.getColumns(null, null, tableName, null);
	// while (rs.next()) {
	// // if (colName.equals(rs.getString("COLUMN_NAME")))
	// // column.setComments(rs.getString("REMARKS"));
	// }
	// // 获取主键列
	// ResultSet rs2 = dbmd.getPrimaryKeys(null, null, tableName);
	// while (rs2.next()) {
	// // if (colName.equals(rs2.getString("COLUMN_NAME")))
	// // column.setColumnKey("TRUE");
	// }
	//
	// }
	// }

	/**
	 * 获得表数据
	 * 
	 * @Title: getDbInfo
	 * @return
	 * @throws SQLException
	 */
	public DatabaseBean getDbInfo(String tableNamePattern) throws SQLException {

		DatabaseBean databaseBean = new DatabaseBean();

		// 表队列
		List<TableBean> tableList = new ArrayList<TableBean>();

		// 初始化数据库
		getConnectionByJDBC();

		// 获取数据库信息
		DatabaseMetaData dbmd = conn.getMetaData();

		databaseBean.setDatabaseProductName(dbmd.getDatabaseProductName());

		// 获得数据库表
		ResultSet rs = dbmd.getTables(null, null, tableNamePattern, new String[] { "TABLE",
				"VIEW" });

		// String tableName = "";
		while (rs.next()) {
			TableBean tempTable = new TableBean();

			// if(rs.getString("TABLE_TYPE").equals("TABLE"))
			// {
			tempTable.setTableName(rs.getString("TABLE_NAME"));
			tempTable.setTableComment(rs.getString("REMARKS"));
			tempTable.setTableSchem(rs.getString(1));
			
			
//			System.out.println(rs.getString("TABLE_CAT"));
//			System.out.println(rs.getString("TABLE_SCHEM"));
//			System.out.println(rs.getString("TABLE_NAME"));
//			System.out.println(rs.getString("TABLE_TYPE"));
//			System.out.println(rs.getString("REMARKS"));
//			System.out.println(rs.getString("TYPE_CAT"));
//			System.out.println(rs.getString("TYPE_SCHEM"));
//			System.out.println(rs.getString("TYPE_NAME"));
//			System.out.println(rs.getString("SELF_REFERENCING_COL_NAME"));
//			System.out.println(rs.getString("REF_GENERATION"));
			
			
			// }

			// 设置列信息
			ResultSet rscol = dbmd.getColumns(null, null,
					tempTable.getTableName(), null);

			ColumnBean tempColumn;

			while (rscol.next()) {
				tempColumn = new ColumnBean();
				tempColumn.setColumnName(rscol.getString("COLUMN_NAME"));
				tempColumn.setColumnType(Integer.parseInt(rscol
						.getString("DATA_TYPE")));
				
				String remarks=rscol.getString("REMARKS");
				if(remarks.length()<1)remarks=".";
				//列中文名称
				tempColumn.setColumnComment(remarks.substring(0,remarks.indexOf(".")));
				//列详细注释
				tempColumn.setColumnCommentDetail(remarks.substring(remarks.indexOf("."),remarks.length()));
				
				tempColumn.setAutoIncrement(rscol.getString("IS_AUTOINCREMENT")
						.equals("YES"));
				tempColumn.setNullAble(rscol.getString("IS_AUTOINCREMENT")
						.equals("YES"));

				// 添加列到表中
				tempTable.getColumnList().add(tempColumn);

			}

			// 设置主键列
			ResultSet rsPrimary = dbmd.getPrimaryKeys(null, null,
					tempTable.getTableName());
			while (rsPrimary.next()) {
				if (rsPrimary.getString("COLUMN_NAME") != null) {

					for (int i = 0; i < tempTable.getColumnList().size(); i++) {
						ColumnBean coltemp = tempTable.getColumnList().get(i);
						if (coltemp.getColumnName().equals(
								rsPrimary.getString("COLUMN_NAME"))) {
							coltemp.setPrimary(true);
						}
					}

				}
			}

			// 设置外键列
			ResultSet rsFPrimary = dbmd.getImportedKeys(null, null,
					tempTable.getTableName());
			while (rsFPrimary.next()) {
				
				
				for (int i = 0; i < tempTable.getColumnList().size(); i++) {
					ColumnBean coltemp = tempTable.getColumnList().get(i);
					if (coltemp.getColumnName().equals(
							rsFPrimary.getString("FKCOLUMN_NAME"))) {
						//System.out.println("FKCOLUMN_NAME "+rsFPrimary.getString("FKCOLUMN_NAME"));
						coltemp.setForeignKey(true);
					}
				}

				
//					System.out.println("======"+tempTable.getTableName()+"=======");
//				
//					System.out.println("PKTABLE_NAME"+rsFPrimary.getString("PKTABLE_NAME"));
//					
//					System.out.println("FKCOLUMN_NAME "+rsFPrimary.getString("FKCOLUMN_NAME"));
//					
//					System.out.println("=============");
					// for(int i=0;i<tempTable.getColumnList().size();i++)
					// {
					// ColumnBean coltemp=tempTable.getColumnList().get(i);
					// if(coltemp.getColumnName().equals(rsPrimary.getString("COLUMN_NAME")))
					// {
					// coltemp.setPrimary(true);
					// }
					// }

				
			}

			tableList.add(tempTable);

		}

		databaseBean.setTableList(tableList);
		return databaseBean;
	}

}
